package net.techfuser.sync;

import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

public class DbUtils {
    private static final Logger log = LoggerFactory.getLogger(DbUtils.class);

    public static Connection getConnection(String driver, String url, String user, String passwd) throws SQLException, ClassNotFoundException {
        Class.forName(driver);            // 加载驱动程序
        return DriverManager.getConnection(url, user, passwd);    // 获取连接
    }



    /**
     * 绑定元素
     *
     * @param pstmt
     * @param values
     * @throws SQLException
     */
    private static void preparedStatementBind(PreparedStatement pstmt, List<Object> values) throws SQLException {
        for (int j = 0; j < values.size(); j++) {
            Object val = values.get(j);
            log.debug((j + 1) + " 预绑定sql:" + val);
            if (val instanceof Date) {
                val = new Timestamp(((Date) val).getTime());
            }
            pstmt.setObject(j + 1, val);
        }
    }


    /**
     * close 释放资源
     *
     * @param closeable
     */
    public static void releaseResource(AutoCloseable... closeable){
        if(null != closeable && closeable.length > 0 ){
            for (AutoCloseable o : closeable){
                if(null != o){
                    try {
                        o.close();
                        log.debug(o.getClass().getName() + " ,close 释放资源成功");
                    } catch (Exception e) {
                        log.debug(o.getClass().getName() + " ,close 异常", e);
                    }
                }
            }
        }
    };


    public static boolean insert(Connection conn, String tableName, Map<String, Object> dataMap) throws SQLException {
        if (null == conn) {
            log.warn("conn is null");
            return false;
        }

        if (StringUtils.isBlank(tableName)) {
            log.warn("tableName is blank");
            return false;
        }

        if (null == dataMap || dataMap.size() == 0) {
            log.warn("dataMap is empty");
            return false;
        }

        //去 null 操作
        int size = dataMap.size();
        List<String> fields = new ArrayList<String>(size);
        List<Object> values = new ArrayList<Object>(size);
        StringBuffer placeholders = new StringBuffer();
        for (Entry<String, Object> entry : dataMap.entrySet()) {
            String key = entry.getKey();
            Object value = entry.getValue();
            if (null != key && null != value) {
                fields.add(key);
                values.add(value);
                placeholders.append(",?");
            }
        }

        String sql = String.format("insert into %s(%s) values(%s)", tableName, String.join(",", fields), placeholders.toString().substring(1));
        log.debug("insert sql: {}", sql);

        PreparedStatement pstmt = null;
        boolean result = false;
        try {
            pstmt = conn.prepareStatement(sql);    // 实例化预编译语句
            preparedStatementBind(pstmt, values);
            result = pstmt.execute();
            log.debug("insert execute finishion. result: {}", result);
        } catch (Exception e) {
            throw e;
        } finally {
            DbUtils.releaseResource(pstmt);
        }

        return result;
    }


    public static int delete(Connection conn, String tableName, Map<String, Object> wheres) throws SQLException {
        StringBuilder sb = new StringBuilder();
        List<Object> values = new ArrayList<Object>();
        for(String key : wheres.keySet()){
            sb.append("AND " + key + " = ? ");
            values.add(wheres.get(key));
        }

        String sql = "DELETE FROM " + tableName + " WHERE " + sb.substring(3);
        return excuteUpdate(conn, sql, values);
    }

    public static int count(Connection conn, String countSql, List<Object> params) throws SQLException {
        int count = 0;
        log.debug("countSql:{}", countSql);
        // 实例化预编译语句
        PreparedStatement pstmt = conn.prepareStatement(countSql);
        preparedStatementBind(pstmt, params);

        ResultSet rs = pstmt.executeQuery();
        if (rs.next()) {
            count = rs.getInt(1);
            log.debug("count result:{}", count);
        }

        return count;
    }

    public static int excuteUpdate(Connection conn, String tableName, Map<String, Object> sets, Map<String, Object> wheres) throws SQLException {
        StringBuilder sb = new StringBuilder();
        List<Object> values = new ArrayList<Object>();
        for(String key : sets.keySet()){
            sb.append(", " + key + " = ? ");
            values.add(sets.get(key));
        }

        String sql = "UPDATE " + tableName +" SET " + sb.toString().substring(1);
        sb = new StringBuilder();
        for(String key : wheres.keySet()){
            sb.append("AND " + key + " = ? ");
            values.add(wheres.get(key));
        }

        sql += " WHERE " + sb.toString().substring(3);
        System.out.println(sql);
        return excuteUpdate(conn, sql, values);
    }


    public static int excuteUpdate(Connection conn, String sql, List<Object> params) throws SQLException {
        log.debug("excuteUpdate:{}", sql);
        PreparedStatement pstmt = conn.prepareStatement(sql);    // 实例化预编译语句
        preparedStatementBind(pstmt, params);

        int result =  pstmt.executeUpdate();
        DbUtils.releaseResource(pstmt);

        return result;
    }

}
